package com.gbase8c.dmt.db.opengauss;

import com.gbase8c.dmt.db.meta.AbstractMeta;

import com.gbase8c.dmt.db.meta.ExtTypes;
import com.gbase8c.dmt.model.migration.config.Snapshot;
import com.gbase8c.dmt.model.migration.dto.DataSourceDto;
import com.gbase8c.dmt.model.migration.dto.RoleDto;
import com.gbase8c.dmt.model.migration.dto.UserDto;
import com.gbase8c.dmt.model.migration.config.Type;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import com.google.common.collect.Sets;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Types;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class MetaImpl extends AbstractMeta {

    private static final List<String> dataTypes = Lists.newArrayList(
            "int1","int2", "int4", "oid", "int8", "integer", "bigint",
//            "tinyint","smallint","binary_integer",
            "money", "numeric", "float4", "float8","real",
            "char", "character", "nchar", "bpchar", "varchar", "text","nvarchar","nvarchar2",
            "name", "bytea", "bool", "bit", "blob","clob","text",
            "date", "time", "timetz", "timestamp",
            "timestamptz", "refcursor", "json", "point",
            "bigint", "bigserial", "bit varying",
            "character varying", "double precision", "interval", "time without time zone",
            "time with time zone", "timestamp without time zone", "timestamp with time zone", "lseg",
            "json",
            "cidr","inet","macaddr",
            "abstime","reltime","smalldatetime",
            "int4range","int8range","numrange","tsrange","tstzrange","daterange",
            "tsquery","tsvector",
            "raw","uuid","jsonb","hll",
            "hash16","hash32","xml",
            "box","circle","path","polygon"
    );

    private static final Map<String, Integer> dataType2SqlType = Maps.newHashMap();
    private static final Map<Integer, String> sqlType2DataType = Maps.newHashMap();

    static {
        // init dataType2SqlType
        dataType2SqlType.put("int2", Types.SMALLINT);
        dataType2SqlType.put("int4", Types.INTEGER);
        dataType2SqlType.put("oid", Types.BIGINT);
        dataType2SqlType.put("int8", Types.BIGINT);
        dataType2SqlType.put("integer", Types.INTEGER);
        dataType2SqlType.put("bigint", Types.BIGINT);

        dataType2SqlType.put("money", Types.DOUBLE);
        dataType2SqlType.put("numeric", Types.NUMERIC);
        dataType2SqlType.put("float4", Types.FLOAT);
        dataType2SqlType.put("float8", Types.DOUBLE);

        dataType2SqlType.put("char", Types.CHAR);
        dataType2SqlType.put("nchar", Types.NCHAR);
        dataType2SqlType.put("bpchar", Types.CHAR);
        dataType2SqlType.put("varchar", Types.VARCHAR);
        dataType2SqlType.put("text", Types.VARCHAR);

        dataType2SqlType.put("name", Types.VARCHAR);
        dataType2SqlType.put("bytea", Types.BINARY);
        dataType2SqlType.put("blob", Types.BLOB);
        dataType2SqlType.put("clob", Types.CLOB);
        dataType2SqlType.put("bool", Types.BIT);
        dataType2SqlType.put("bit", Types.BIT);

        dataType2SqlType.put("date", Types.DATE);
        dataType2SqlType.put("time", Types.TIME);
        dataType2SqlType.put("timetz", Types.TIME);
        dataType2SqlType.put("timestamp", Types.TIMESTAMP);

        dataType2SqlType.put("timestamptz", Types.TIMESTAMP);
        dataType2SqlType.put("refcursor", Types.REF_CURSOR);
        dataType2SqlType.put("json", Types.OTHER);
        dataType2SqlType.put("point", Types.OTHER);

        dataType2SqlType.put("character", Types.CHAR);
        dataType2SqlType.put("character varying", Types.VARCHAR);
        dataType2SqlType.put("timestamp without time zone", Types.TIMESTAMP);
        dataType2SqlType.put("timestamp with time zone", Types.TIMESTAMP);

        dataType2SqlType.put("time without time zone", Types.TIME);
        dataType2SqlType.put("time with time zone", Types.TIME);
        dataType2SqlType.put("double percision", Types.DOUBLE);
        dataType2SqlType.put("bit varying", Types.BIT);

        dataType2SqlType.put("json", ExtTypes.JSON);

        //init sqlType2DataType
        sqlType2DataType.put(Types.SMALLINT, "int2");
        sqlType2DataType.put(Types.INTEGER, "int4");
//        sqlType2DataType.put(Types.BIGINT, "oid");
        sqlType2DataType.put(Types.BIGINT, "int8");
        sqlType2DataType.put(Types.TINYINT, "int1");

        sqlType2DataType.put(Types.BOOLEAN,"bool");

        sqlType2DataType.put(ExtTypes.MONEY, "money");
        sqlType2DataType.put(Types.NUMERIC, "numeric");
        sqlType2DataType.put(Types.FLOAT, "float4");
        sqlType2DataType.put(Types.DOUBLE, "float8");
        sqlType2DataType.put(Types.REAL,"real");

        sqlType2DataType.put(Types.CHAR, "char");
        sqlType2DataType.put(Types.NCHAR, "nchar");
        sqlType2DataType.put(Types.VARCHAR, "varchar");
        sqlType2DataType.put(Types.NVARCHAR, "nvarchar2");
        sqlType2DataType.put(Types.BINARY, "bytea");
        sqlType2DataType.put(Types.BIT, "bit");

        sqlType2DataType.put(Types.BLOB, "blob");
        sqlType2DataType.put(Types.CLOB, "clob");
        sqlType2DataType.put(Types.NCLOB, "text");
        sqlType2DataType.put(Types.ROWID, "bytea");
        sqlType2DataType.put(Types.LONGVARCHAR,"text");

        sqlType2DataType.put(Types.DATE, "date");
        sqlType2DataType.put(Types.TIMESTAMP, "timestamp");
        sqlType2DataType.put(Types.REF_CURSOR, "refcursor");
        sqlType2DataType.put(Types.TIME, "time");
        sqlType2DataType.put(Types.TIMESTAMP_WITH_TIMEZONE, "timestamp with time zone");
        sqlType2DataType.put(ExtTypes.INTERVAL,"interval");

        sqlType2DataType.put(ExtTypes.JSON, "json");

        sqlType2DataType.put(ExtTypes.RAW,"raw");
        sqlType2DataType.put(ExtTypes.UUID,"uuid");
        sqlType2DataType.put(ExtTypes.JSONB,"jsonb");
        sqlType2DataType.put(ExtTypes.HLL,"hll");

        sqlType2DataType.put(ExtTypes.CIDR,"cidr");
        sqlType2DataType.put(ExtTypes.INET,"inet");
        sqlType2DataType.put(ExtTypes.MACADDR,"macaddr");

        sqlType2DataType.put(ExtTypes.ABSTIME,"abstime");
        sqlType2DataType.put(ExtTypes.RELTIME,"reltime");
        sqlType2DataType.put(ExtTypes.SMALLDATETIME,"smalldatetime");

        sqlType2DataType.put(ExtTypes.INT4RANGE,"int4range");
        sqlType2DataType.put(ExtTypes.INT8RANGE,"int8range");
        sqlType2DataType.put(ExtTypes.NUMRANGE,"numrange");
        sqlType2DataType.put(ExtTypes.TSRANGE,"tsrange");
        sqlType2DataType.put(ExtTypes.TSTZRANGE,"tstzrange");
        sqlType2DataType.put(ExtTypes.DATERANGE,"daterange");

        sqlType2DataType.put(ExtTypes.TSQUERY,"tsquery");
        sqlType2DataType.put(ExtTypes.TSVECTOR,"tsvector");

        sqlType2DataType.put(ExtTypes.HASH16,"hash16");
        sqlType2DataType.put(ExtTypes.HASH32,"hash32");
        sqlType2DataType.put(ExtTypes.XML,"xml");
        sqlType2DataType.put(ExtTypes.XMLTYPE,"xml");

        sqlType2DataType.put(ExtTypes.POINT,"point");
        sqlType2DataType.put(ExtTypes.BOX,"box");
        sqlType2DataType.put(ExtTypes.CIRCLE,"circle");
        sqlType2DataType.put(ExtTypes.PATH,"path");
        sqlType2DataType.put(ExtTypes.POLYGON,"polygon");
        sqlType2DataType.put(ExtTypes.LSEG,"lseg");

        sqlType2DataType.put(ExtTypes.ENUM,"ENUMTYPE");
        sqlType2DataType.put(ExtTypes.SET,"SETTYPE");
    }

    // 需要指定长度的字段集合
    public static final Set<String> needSetLengthSet = Sets.newHashSet(
            "bit", "char", "varchar", "numeric","nvarchar2","nchar",
            "time", "timestamp", "varbit", "timestamptz",
            "timetz","interval");
    // 需要指定精度的时间类型集合
    public static final Set<Integer> timeTypeSet = Sets.newHashSet(
            Types.TIME,Types.TIMESTAMP,Types.TIMESTAMP_WITH_TIMEZONE,Types.TIME_WITH_TIMEZONE);

    private static final Set<String> KEYWORDS = Sets.newHashSet(
            "all", "analyse", "analyze", "and", "any", "array", "as", "asc", "asymmetric", "authid", "authorization",
            "binary", "both", "buckets",
            "case", "cast", "check", "collate", "collation", "column", "compact", "concurrently", "constraint", "create",
            "cross", "csn","current_catalog", "current_date", "current_role", "current_schema", "current_time",
            "current_timestamp", "current_user", "deltamerge", "default", "deferrable", "desc", "distinct", "do",
            "else", "end", "except", "excluded",
            "false", "fetch", "fenced", "for", "foreign", "freeze", "from", "full",
            "grant", "group",
            "having", "hdfsdirectory",
            "ilike", "in", "initially", "inner", "intersect", "into", "is",
            "join",
            "leading", "left", "less", "like", "limit", "localtime", "localtimestamp",
            "maxvalue", "minus", "modify",
            "natural", "not", "notnull", "null",
            "offset", "on", "only", "or", "order", "outer", "overlaps",
            "performance", "placing", "primary", "priorer", "procedure",
            "recyclebin","references", "reject", "returning", "right", "rownum",
            "select", "session_user", "similar", "some", "symmetric", "sysdate",
            "table", "tablesample", "then", "to", "trailing", "true",
            "union", "unique", "user", "using",
            "variadic", "verbose", "verify", "when", "where", "window", "with");

    private static final Set<String> FCT_TYPE_KEYWORDS = Sets.newHashSet("authorization", "binary", "collation", "concurrently", "cross", "current_schema", "freeze",
            "full", "ilike", "inner", "is", "isnull", "join", "left", "like", "natural", "notnull", "outer", "overlaps",
            "right", "similar", "tablesample", "verbose");

    private static final Set<String> SYSTEM_FIELDS = Sets.newHashSet("oid", "tableoid", "xmin", "xmin", "cmin", "xmax", "cmax", "ctid" , "tid");

    private static final Set<String> exWords = Sets.newHashSet("function", "package", "procedure", "performance");

    // alphanumeric
    public final static String ALPHANUMERIC_REGEX = "^[a-z\\_][a-z0-9\\_]+$";

    public final static Pattern pattern = Pattern.compile(ALPHANUMERIC_REGEX);

    private boolean isReservedWords(String name) {
        if (exWords.contains(name)
                || KEYWORDS.contains(name)
                || FCT_TYPE_KEYWORDS.contains(name)
                || SYSTEM_FIELDS.contains(name)) {
        return true;
    }
        return false;
}

    // todo 这里有个bug: 如果name为SYSTEM_FIELDS, 不管quote不quote, 数据库都会报错. ora2pg也如此.
    private String quoteObjectName(String name, boolean preserveCase) {
        if (!preserveCase) {
            name = name.toLowerCase();
            Matcher matcher = pattern.matcher(name);
            if (isReservedWords(name) || !matcher.matches()) {
                return "\"" + name + "\"";
            } else {
                return name;
            }
        } else {
//            return "\"" + name + "\"";
            Matcher matcher = pattern.matcher(name);
            if (isReservedWords(name) || !matcher.matches()) {
                return "\"" + name + "\"";
            } else {
                return name;
            }
        }
    }

    public MetaImpl(DataSourceDto dataSourceDto) {
        super(dataSourceDto);
    }

    @Override
    public List<String> keywords() {
        List<String> keywords = Lists.newArrayList();
        keywords.addAll(exWords);
        keywords.addAll(KEYWORDS);
        keywords.addAll(FCT_TYPE_KEYWORDS);
        keywords.addAll(SYSTEM_FIELDS);
        return keywords;
    }

    @Override
    public String characterSet(String database) {
        String sql = "select getdatabaseencoding()";
        String characterSet = query(sql, new ScalarHandler<String>());
        return characterSet;
    }

    @Override
    public Snapshot snapshot() {
        throw new RuntimeException("unsupport now!");
    }

    @Override
    public boolean valid(Snapshot snapshot) {
        throw new RuntimeException("unsupport now!");
    }

    @Override
    public List<String> getDataTypes() {
        return dataTypes;
    }

    @Override
    public Integer toSqlType(String dataType) {
        return dataType2SqlType.getOrDefault(dataType, null);
    }

    @Override
    public String toDataType(Integer sqlType) {
        return sqlType2DataType.getOrDefault(sqlType, null);
    }

    @Override
    public String wrap(String name, boolean preserveCase) {
        return quoteObjectName(name, preserveCase);
    }

    public List<String> getKeywords() {
        String sql = "select word from pg_get_keywords() where catcode in ('T','R')";
        List<String> keywords = query(sql, new ColumnListHandler<String>());
        keywords.add("ctid");
        // todo 发现新的keywords时请添加
        return keywords;
    }

    public String getGbase8cDbType() {
        String sql = "select version()";
        return query(sql, new ScalarHandler<String>());
    }

    public List<String> getTableSpaceNames() {
        String sql = "select spcname from pg_catalog.pg_tablespace le";
        return query(sql, new ColumnListHandler<String>());
    }

    public boolean convertType(Type type, boolean needConverted) {
        boolean convertible = true;
        if (needConverted) {
            String tarDataType = toDataType(type.getSqlType());
            if (tarDataType == null) {
                convertible = Boolean.FALSE;
            }
            type.setTarDataType(tarDataType);
            type.setTarType(tarType(type));
        } else {
            type.setTarDataType(type.getDataType());
            type.setTarType(type.getSrcType());
        }
        type.setTarDataLength(type.getDataLength());
        type.setTarDataPrecision(type.getDataPrecision());
        type.setTarDataScale(type.getDataScale());
        return convertible;
    }

    public String tarType(Type type) {
        StringBuilder sb = new StringBuilder();
        String dataType = type.getTarDataType();
        sb.append(dataType);
        if (needSetLengthSet.contains(dataType)) {
            //如果说是numeric类型，是这个样子的 "a23" numeric(255,6)
            if (dataType.equals("numeric")) {
                Integer dataPrecision = type.getDataPrecision();
                Integer dataScale = type.getDataScale();
                if (dataPrecision != null) {
                    sb.append("(").append(dataPrecision);
                    if (dataScale != null) {
                        sb.append(", ").append(dataScale);
                    }
                    sb.append(")");
                }
            } else {
                //不是numeric字段的 是这个样子的 "a12" varbit(255)
                Integer dataLength = type.getDataLength();
                if (dataLength != null) {
                    sb.append("(").append(dataLength).append(")").append(" ");
                }
            }
        }
        return sb.toString();
    }

    public static List<String> sysPrivilege2String(UserDto.SysPrivDto userSysPrivDto,
                                                   RoleDto.SysPrivDto roleSysPrivDto){

        List<String> list = Lists.newArrayList();
        if (userSysPrivDto!= null && roleSysPrivDto == null){
            if (userSysPrivDto.getSuperUserPriv().equals("Y")){
                list.add(" SYSADMIN ");
            }
            if (userSysPrivDto.getInheritPriv().equals("Y")){
                list.add(" INHERIT ");
            }
            if (userSysPrivDto.getCreatDbPriv().equals("Y")){
                list.add(" CREATEDB ");
            }
            if (userSysPrivDto.getCreatRolePriv().equals("Y")){
                list.add(" CREATEROLE ");
            }
            if (userSysPrivDto.getLoginPriv().equals("Y")){
                list.add(" LOGIN ");
            }
            if (userSysPrivDto.getReplicationPriv().equals("Y")){
                list.add(" REPLICATION ");
            }
            if (userSysPrivDto.getBypassRlsPriv().equals("Y")){
                list.add(" BYPASSRLS ");
            }
        } else if (roleSysPrivDto!= null && userSysPrivDto == null){
            if (roleSysPrivDto.getSuperUserPriv().equals("Y")){
                list.add(" SYSADMIN ");
            }
            if (roleSysPrivDto.getInheritPriv().equals("Y")){
                list.add(" INHERIT ");
            }
            if (roleSysPrivDto.getCreatDbPriv().equals("Y")){
                list.add(" CREATEDB ");
            }
            if (roleSysPrivDto.getCreatRolePriv().equals("Y")){
                list.add(" CREATEROLE ");
            }
            if (roleSysPrivDto.getLoginPriv().equals("Y")){
                list.add(" LOGIN ");
            }
            if (roleSysPrivDto.getReplicationPriv().equals("Y")){
                list.add(" REPLICATION ");
            }
            if (roleSysPrivDto.getBypassRlsPriv().equals("Y")){
                list.add(" BYPASSRLS ");
            }
        }
        return list;
    }

    public static String databasePrivilege2String(UserDto.DatabasePrivDto userDatabasePrivDto,
                                                  RoleDto.DatabasePrivDto roleDatabasePrivDto){
        StringBuilder sb = new StringBuilder();
        if (userDatabasePrivDto!= null && roleDatabasePrivDto == null){
            if (userDatabasePrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (userDatabasePrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (userDatabasePrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (userDatabasePrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
//            if(databasePrivDto.getCreatePriv().equals("Y")){
//                sb.append(",CREATE ");
//            }
            if (userDatabasePrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
            if (userDatabasePrivDto.getExecutePriv().equals("Y")){
                sb.append(",EXECUTE ");
            }
            if (userDatabasePrivDto.getTriggerPriv().equals("Y")){
                sb.append(",TRIGGER ");
            }
        } else if (roleDatabasePrivDto != null && userDatabasePrivDto == null){
            if (roleDatabasePrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (roleDatabasePrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (roleDatabasePrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (roleDatabasePrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
//            if(roleDatabasePrivDto.getCreatePriv().equals("Y")){
//                sb.append(",CREATE ");
//            }
            if (roleDatabasePrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
            if (roleDatabasePrivDto.getExecutePriv().equals("Y")){
                sb.append(",EXECUTE ");
            }
            if (roleDatabasePrivDto.getTriggerPriv().equals("Y")){
                sb.append(",TRIGGER ");
            }
        }
        return sb.toString();
    }

    public static String tablePrivilege2String(UserDto.TablePrivDto userTablePrivDto,
                                               RoleDto.TablePrivDto roleTablePrivDto){
        StringBuilder sb = new StringBuilder();
        if (userTablePrivDto != null && roleTablePrivDto == null){
            if (userTablePrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (userTablePrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (userTablePrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (userTablePrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
            if (userTablePrivDto.getTruncatePriv().equals("Y")){
                sb.append(",TRUNCATE ");
            }
            if (userTablePrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
            if (userTablePrivDto.getTriggerPriv().equals("Y")){
                sb.append(",TRIGGER ");
            }
        } else if (roleTablePrivDto != null && userTablePrivDto == null){
            if (roleTablePrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (roleTablePrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (roleTablePrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (roleTablePrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
            if (roleTablePrivDto.getTruncatePriv().equals("Y")){
                sb.append(",TRUNCATE ");
            }
            if (roleTablePrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
            if (roleTablePrivDto.getTriggerPriv().equals("Y")){
                sb.append(",TRIGGER ");
            }
        }
        return sb.toString();
    }

    //用户和角色的列级权限用一个方法
    public static String columnPrivilege2String(UserDto.ColumnPrivDto userColumnPrivDto,
                                                RoleDto.ColumnPrivDto roleColumnPrivDto){
        StringBuilder sb = new StringBuilder();
        if (userColumnPrivDto != null && roleColumnPrivDto == null){
            if (userColumnPrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (userColumnPrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (userColumnPrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (userColumnPrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
            if (userColumnPrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
        } else if (roleColumnPrivDto != null && userColumnPrivDto == null){
            if (roleColumnPrivDto.getSelectPriv().equals("Y")){
                sb.append(",SELECT ");
            }
            if (roleColumnPrivDto.getInsertPriv().equals("Y")){
                sb.append(",INSERT ");
            }
            if (roleColumnPrivDto.getDeletePriv().equals("Y")){
                sb.append(",DELETE ");
            }
            if (roleColumnPrivDto.getUpdatePriv().equals("Y")){
                sb.append(",UPDATE ");
            }
            if (roleColumnPrivDto.getReferencesPriv().equals("Y")){
                sb.append(",REFERENCES ");
            }
        }
        return sb.toString();
    }
}
